<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:template match="Class">
		<xsl:variable name="Name" select="Name"/>
		<xsl:variable name="SingleCount" select="count(ClassAttribs/ClassAttributes[CType='single'])"/>

PRINT 'Working on object [<xsl:value-of select="Name"/>]'


CREATE TABLE [<xsl:value-of select="Name"/>] (
	[<xsl:value-of select="Name"/>ID] [uniqueidentifier] NOT NULL,
	[Created] [datetime] NOT NULL,
	[Modified] [datetime] NOT NULL,
		<xsl:for-each select="ClassAttribs/ClassAttributes">
			<xsl:if test="CType='single'">
	[<xsl:value-of select="Name"/>] <xsl:value-of select="SqlType"/> NOT NULL,
			</xsl:if>
		</xsl:for-each>
	CONSTRAINT [PK_<xsl:value-of select="Name"/>] PRIMARY KEY CLUSTERED ( [<xsl:value-of select="Name"/>ID] ) ON [PRIMARY]
) ON [PRIMARY]







CREATE TABLE [<xsl:value-of select="Name"/>_Audit] (
	[<xsl:value-of select="Name"/>ID] [uniqueidentifier] NOT NULL,
	[Created] [datetime] NOT NULL,
	[Modified] [datetime] NOT NULL,
		<xsl:for-each select="ClassAttribs/ClassAttributes">
			<xsl:if test="CType='single'">
	[<xsl:value-of select="Name"/>] <xsl:value-of select="SqlType"/> NOT NULL,
			</xsl:if>
		</xsl:for-each>
	CONSTRAINT [PK_<xsl:value-of select="Name"/>_Audit] PRIMARY KEY CLUSTERED ( [<xsl:value-of select="Name"/>ID] ) ON [PRIMARY]
) ON [PRIMARY]






		<xsl:for-each select="ClassAttribs/ClassAttributes">
			<xsl:if test="CType='array'">
CREATE TABLE [<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>] (
	[<xsl:value-of select="$Name"/>ID] [uniqueidentifier] NOT NULL,
	[<xsl:value-of select="Type"/>bID] [uniqueidentifier] NOT NULL,
	CONSTRAINT [PK_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>] PRIMARY KEY CLUSTERED (
		[<xsl:value-of select="$Name"/>ID],	
		[<xsl:value-of select="Type"/>bID]
	) ON [PRIMARY]
) ON [PRIMARY]	
			</xsl:if>
		</xsl:for-each>


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>SelectAll AS
	SELECT <xsl:value-of select="Name"/>ID FROM [<xsl:value-of select="Name"/>] <xsl:for-each select="ClassAttribs/ClassAttributes[ToString='true']">order by [<xsl:value-of select="Name"/>]</xsl:for-each>
GO

CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>CountAll AS
	SELECT COUNT(*) FROM [<xsl:value-of select="Name"/>]
GO


CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Select (@<xsl:value-of select="Name"/>ID uniqueidentifier) AS
	SELECT [<xsl:value-of select="Name"/>ID],[created],[modified],
		<xsl:for-each select="ClassAttribs/ClassAttributes">
			<xsl:if test="CType='single'">
				<xsl:if test="position()=$SingleCount">[<xsl:value-of select="Name"/>]</xsl:if>
				<xsl:if test="position()!=$SingleCount">[<xsl:value-of select="Name"/>],</xsl:if>
			</xsl:if>
		</xsl:for-each>
	FROM [<xsl:value-of select="Name"/>] WHERE [<xsl:value-of select="Name"/>ID]=@<xsl:value-of select="Name"/>ID
	
		<xsl:for-each select="ClassAttribs/ClassAttributes">
			<xsl:if test="CType='array'">
	SELECT [<xsl:value-of select="Type"/>bID] FROM <xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/> WHERE
		[<xsl:value-of select="$Name"/>ID]=@<xsl:value-of select="$Name"/>ID
			</xsl:if>
		</xsl:for-each>
GO



CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Select_Audit (@<xsl:value-of select="Name"/>ID uniqueidentifier) AS
	SELECT [<xsl:value-of select="Name"/>ID],[created],[modified],
		<xsl:for-each select="ClassAttribs/ClassAttributes">
			<xsl:if test="CType='single'">
				<xsl:if test="position()=$SingleCount">[<xsl:value-of select="Name"/>]</xsl:if>
				<xsl:if test="position()!=$SingleCount">[<xsl:value-of select="Name"/>],</xsl:if>
			</xsl:if>
		</xsl:for-each>
	FROM [<xsl:value-of select="Name"/>_Audit] WHERE [<xsl:value-of select="Name"/>ID]=@<xsl:value-of select="Name"/>ID
	
		<xsl:for-each select="ClassAttribs/ClassAttributes">
			<xsl:if test="CType='array'">
	SELECT [<xsl:value-of select="Type"/>bID] FROM <xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/> WHERE
		[<xsl:value-of select="$Name"/>ID]=@<xsl:value-of select="$Name"/>ID
			</xsl:if>
		</xsl:for-each>
GO



CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Delete (@<xsl:value-of select="Name"/>ID uniqueidentifier) AS
	<xsl:for-each select="ClassAttribs/ClassAttributes">
		<xsl:if test="CType='array'">
	DELETE FROM [<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>] WHERE [<xsl:value-of select="$Name"/>ID]=@<xsl:value-of select="$Name"/>ID	
		</xsl:if>
	</xsl:for-each>
	DELETE FROM [<xsl:value-of select="Name"/>] WHERE [<xsl:value-of select="Name"/>ID]=@<xsl:value-of select="Name"/>ID
GO


CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Insert_Audit (@<xsl:value-of select="Name"/>ID uniqueidentifier,
<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='single'">
		<xsl:if test="position()=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>)</xsl:if>
		<xsl:if test="position()!=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>,</xsl:if>
	</xsl:if>
</xsl:for-each>
AS
	INSERT INTO [<xsl:value-of select="Name"/>_Audit]([<xsl:value-of select="Name"/>ID],[created],[modified],
<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='single'">
		<xsl:if test="position()=$SingleCount">[<xsl:value-of select="Name"/>])</xsl:if>
		<xsl:if test="position()!=$SingleCount">[<xsl:value-of select="Name"/>],</xsl:if>
	</xsl:if>
</xsl:for-each>

	VALUES(@<xsl:value-of select="Name"/>ID,getDate(),getDate(),
<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='single'">
		<xsl:if test="position()=$SingleCount">@<xsl:value-of select="Name"/>)</xsl:if>
		<xsl:if test="position()!=$SingleCount">@<xsl:value-of select="Name"/>,</xsl:if>	
	</xsl:if>
</xsl:for-each>
GO

CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Insert (@<xsl:value-of select="Name"/>ID uniqueidentifier,
<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='single'">
		<xsl:if test="position()=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>)</xsl:if>
		<xsl:if test="position()!=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>,</xsl:if>
	</xsl:if>
</xsl:for-each>
AS
	INSERT INTO [<xsl:value-of select="Name"/>]([<xsl:value-of select="Name"/>ID],[created],[modified],
<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='single'">
		<xsl:if test="position()=$SingleCount">[<xsl:value-of select="Name"/>])</xsl:if>
		<xsl:if test="position()!=$SingleCount">[<xsl:value-of select="Name"/>],</xsl:if>
	</xsl:if>
</xsl:for-each>

	VALUES(@<xsl:value-of select="Name"/>ID,getDate(),getDate(),
<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='single'">
		<xsl:if test="position()=$SingleCount">@<xsl:value-of select="Name"/>)</xsl:if>
		<xsl:if test="position()!=$SingleCount">@<xsl:value-of select="Name"/>,</xsl:if>	
	</xsl:if>
</xsl:for-each>
GO

CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Update (@<xsl:value-of select="Name"/>ID uniqueidentifier,
<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='single'">
		<xsl:if test="position()=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>)</xsl:if>
		<xsl:if test="position()!=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>,</xsl:if>
	</xsl:if>
</xsl:for-each>
AS
	UPDATE [<xsl:value-of select="Name"/>] SET [modified]=getDate(),
<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='single'">
		<xsl:if test="position()=$SingleCount">[<xsl:value-of select="Name"/>]=@<xsl:value-of select="Name"/></xsl:if>
		<xsl:if test="position()!=$SingleCount">[<xsl:value-of select="Name"/>]=@<xsl:value-of select="Name"/>,</xsl:if>
	</xsl:if>
</xsl:for-each>
WHERE [<xsl:value-of select="Name"/>ID]=@<xsl:value-of select="Name"/>ID
GO


<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='array'">
CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>Delete(@<xsl:value-of select="$Name"/>ID uniqueidentifier)
AS
	DELETE FROM <xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/> WHERE [<xsl:value-of select="$Name"/>ID]=@<xsl:value-of select="$Name"/>ID
GO

CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>DeleteRef(@<xsl:value-of select="Type"/>ID uniqueidentifier)
AS
	DELETE FROM <xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/> WHERE [<xsl:value-of select="Type"/>bID]=@<xsl:value-of select="Type"/>ID
GO



CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>Insert(@<xsl:value-of select="$Name"/>ID uniqueidentifier,@<xsl:value-of select="Type"/>bID uniqueidentifier)
AS
	INSERT INTO <xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(<xsl:value-of select="$Name"/>ID,<xsl:value-of select="Type"/>bID) VALUES(@<xsl:value-of select="$Name"/>ID,@<xsl:value-of select="Type"/>bID)
GO
	</xsl:if>
</xsl:for-each>


<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="ToString='true'">
CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>Search(@searchString nvarchar(255))
AS
	SELECT <xsl:value-of select="$Name"/>ID FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>] like '%' + @searchString + '%'
GO		
	</xsl:if>
</xsl:for-each>
        
        
-- DEFINED PROCEDURES FOR CONTENT
<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='single'">
        <xsl:choose>
            <xsl:when test="Type='bool'">
                
CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val tinyint)
AS
    SELECT <xsl:value-of select="$Name"/>ID FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
GO
            </xsl:when>
            
            <xsl:when test="Type='DateTime'">
CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_Between(@start_ datetime, @end_ datetime)
AS
    SELECT <xsl:value-of select="$Name"/>ID FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&gt;=@start_ and [<xsl:value-of select="Name"/>]&lt;=@end_
GO 

CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_Before(@val datetime)
AS
    SELECT <xsl:value-of select="$Name"/>ID FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&lt;=@val
GO 

CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_After(@val datetime)
AS
    SELECT <xsl:value-of select="$Name"/>ID FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&gt;=@val
GO 

CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val datetime)
AS
    SELECT <xsl:value-of select="$Name"/>ID FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
GO 

            </xsl:when>
            
            <xsl:when test="Type='int'">
CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val int)
AS
SELECT <xsl:value-of select="$Name"/>ID FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
GO  

CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_GreaterThan(@val int)
AS
SELECT <xsl:value-of select="$Name"/>ID FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&gt;=@val
GO

CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_LessThan(@val int)
AS
SELECT <xsl:value-of select="$Name"/>ID FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&lt;=@val
GO
            </xsl:when>
            
            <xsl:when test="Type='double'">
CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val float)
AS
SELECT <xsl:value-of select="$Name"/>ID FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
GO  

CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_GreaterThan(@val float)
AS
SELECT <xsl:value-of select="$Name"/>ID FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&gt;=@val
GO

CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_LessThan(@val float)
AS
SELECT <xsl:value-of select="$Name"/>ID FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&lt;=@val
GO
            </xsl:when>
            
            
            <xsl:when test="Type='string' and SqlSPType!='ntext'">
CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val nvarchar(255))
AS
SELECT <xsl:value-of select="$Name"/>ID FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
GO                       


CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_Like(@val nvarchar(255))
AS
SELECT <xsl:value-of select="$Name"/>ID FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>] like '%' + @val + '%'
GO   
            </xsl:when>
        </xsl:choose>
    </xsl:if>
</xsl:for-each>



SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO











<![CDATA[





if exists (select * from sysobjects where id = object_id(N'[Paging_RowCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Paging_RowCount]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE Paging_RowCount
(
@Tables varchar(1000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL)
AS

/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
	SET @Sort = @PK

/*Find the @PK type*/
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int

/*Set sorting variables.*/	
IF CHARINDEX('DESC',@Sort)>0
	BEGIN
		SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
		SET @operator = '<='
	END
ELSE
	BEGIN
		IF CHARINDEX('ASC', @Sort) = 0
			SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
		SET @operator = '>='
	END


IF CHARINDEX('.', @strSortColumn) > 0
	BEGIN
		SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
		SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
	END
ELSE
	BEGIN
		SET @SortTable = @Tables
		SET @SortName = @strSortColumn
	END

SELECT @type=t.name, @prec=c.prec
FROM sysobjects o 
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName

IF CHARINDEX('char', @type) > 0
   SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)

/*Default Page Number*/
IF @PageNumber < 1
	SET @PageNumber = 1

/*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))

/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
	BEGIN
		SET @strFilter = ' WHERE ' + @Filter + ' '
		SET @strSimpleFilter = ' AND ' + @Filter + ' '
	END
ELSE
	BEGIN
		SET @strSimpleFilter = ''
		SET @strFilter = ''
	END
IF @Group IS NOT NULL AND @Group != ''
	SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
	SET @strGroup = ''
	
/*Execute dynamic query*/	
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM [' + @Tables + '] ' + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM [' + @Tables + '] WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

]]>




PRINT 'End working on object [<xsl:value-of select="Name"/>]'

	
	</xsl:template>
</xsl:stylesheet>

  